package cn.brilliantfuture.commentry.lockers.impl;

import java.util.List;

import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import cn.brilliantfuture.commentry.entity.Lockers;
import cn.brilliantfuture.commentry.lockers.LockersAddressMessageLocal;

/**
 * 
 * Title: Communication Entry
 * 
 * Author:  LiTian  
 *
 * Date: 2012-3-27
 * 
 * Description:
 * 
 */
@Stateless
public class LockersAddressMessageImpl implements LockersAddressMessageLocal {

	@PersistenceContext(unitName = "commentry-jpa")
	private EntityManager entityManager;
	
	@SuppressWarnings("unchecked")
	public List<Object[]> getLockersAddresses(Integer groupId, Integer businessType) {

		StringBuffer sql = new StringBuffer();
		sql.append("select vl.lockers_id, vl.description, vl.lockers_name, vl.has_general, vl.has_keep_warm, vl.has_thermostat, vl.county_id, vl.county_name, vl.city_id, vl.city_name, vl.province_id, vl.province_name, vl.villiage_id, vl.villiage_name, vl.location_name, vl.latitude, vl.longitude, vl.province_en_name, vl.city_en_name, vl.county_en_name, vl.villiage_en_name, vl.location_en_name, vl.lockers_en_name from view_lockers vl where vl.lockers_used = 1");

		if (null == businessType || businessType.intValue() == 1){
			sql.append(" and vl.group_id = ").append(groupId).append(" and vl.delivery_business = 1");
		} else if (businessType.intValue() == 2){
			sql.append(" and vl.storage_business = 1 group by vl.lockers_id");
		} else if (businessType.intValue() == 3){
			sql.append(" and vl.group_id = ").append(groupId).append(" and vl.refund_business = 1");
		}
		
		//sql.append(" order by convert(vl.province_name using gbk) asc, convert(vl.city_name using gbk) asc, convert(vl.county_name using gbk) asc, convert(vl.villiage_name using gbk) asc, vl.lockers_name asc");
		
		sql.append(" order by vl.province_name asc, vl.city_name asc, vl.county_name asc, vl.villiage_name asc, vl.lockers_name asc");
		
		List<Object[]> list = entityManager.createNativeQuery(sql.toString()).getResultList();
		
		if (null != list)
			if (list.size() > 0){
				return list;
			}else
				return null;
		else
			return null;
	}
	
	@SuppressWarnings("unchecked")
	public List<Object[]> getDataModels() {
		List<Object[]> list = entityManager.createNativeQuery("select dm.data_model_id, dm.model_type, dm.lower_limit, dm.upper_limit from data_models dm where dm.deleted = 0").getResultList();
		if (null != list)
			if (list.size() > 0){
				return list;
			}else
				return null;
		else
			return null;
	}

	@SuppressWarnings("unchecked")
	public JSONArray getStorePoints() {
		JSONArray result = new JSONArray();
		
		List<Lockers> lockersList = entityManager.createQuery("select l from Lockers l where l.deleted = 0 and l.used = true and l.storageBusiness = true ").getResultList(); 
		
		if (null != lockersList && lockersList.size() > 0){
			for (Lockers lockers : lockersList) {
				JSONObject obj = new JSONObject();
				
				obj.accumulate("lockersName", lockers.getLockersName());
				obj.accumulate("location", lockers.getLocation().getName());
				obj.accumulate("village", lockers.getLocation().getVilliage().getName());
				obj.accumulate("latitude", lockers.getLocation().getVilliage().getLatitude());
				obj.accumulate("longitude", lockers.getLocation().getVilliage().getLongitude());
				
				result.add(obj);
			}
		}
		
		return result;
	}

}
